--- layout: default path: google-apps title: Google Apps Scripting --- # Google Apps Scripting --- It all started with a problem... -- -- -- I literally couldn't publish a CSV to the open web. -- This was dumb. -- There had to be a solution. --- After much google searching, I happened upon [Google Apps Script](https://developers.google.com/apps-script/) -- -- Sure, I had heard of it. -- Even tried it. -- Never really _thought_ about it. -- (Disclosure: The UI reminded me of Visual Basic). -- -- But, it had a REST API under the hood, so... --- # First Pass -- https://github.com/18F/sheet-to-csv -- `node index SHEET_ID > out.csv` -- Uses a "Target" (i.e., an event listener) -- -- ``` js /** * This function returns a CSV from Google Drive using the file's id * @param {string} fileId the Google Sheet's fileId * @return {string} a csv string **/ function getCSVbyId(fileId) { var f = SpreadsheetApp.openById(fileId).getActiveSheet().getDataRange().getValues(); var csv = ""; for (i in f) { csv += f[i].join(',') + "\n"; } return csv; } ``` -- `var f = SpreadsheetApp.openById(fileId).getActiveSheet().getDataRange().getValues();` -- But still need something to *call* the Target. -- -- Uses OAuth 2.0 for authentication. -- [Warning: When OAuth is involved, pay attention to scopes!] -- Anyway, a few tweaks later... -- `node index SHEET_ID > out.csv` -- --- # The Second Pass -- We can do *better* -- -- There's also that REST API... -- -- -- -- Now we can make https://github.com/18F/gapps-download -- ``` bash node index 12345 text/html > out.html # Download a google doc to `out.html` node index 12345 text/csv > out.csv # Download a google sheet to `out.csv` node index 12345 > out.pdf # Download a google doc as a pdf node index 12345 application/vnd.openxmlformats-officedocument.wordprocessingml.document > out.docx # Download google doc as a MS Word Document ``` -- And, because we're using the REST API, no Target is needed. -- [Warning: When OAuth is involved, pay attention to scopes!] -- Just a single node script, and now I can download my CSV natively! -- And html, docx, etc. -- --- # Moar Google Scripting! -- Micro-purchase experiment involves a Google Form -- That means **scripting**! -- On formSubmit: 1. Update github 2. Update Form -- -- Remember Triggers? (the Event Listener?) -- -- ``` js function githubPost() { var ss = SpreadsheetApp.openById(ssID) // replace the ssID with the Spreadsheet ID var sheet = ss.getSheetByName("Form Responses 1"); var formResponses = FormApp.getActiveForm().getResponses(); var res = formResponses[formResponses.length-1].getItemResponses(); var amt = res[0].getResponse(); var title = "Load Schedule 70 data into CALC. >>> Current bid: " + amt + " <<<" var payload = { "title": title } var url = "https://api.github.com/repos/18f/calc/issues/255?access_token=" + ACCESS_TOKEN // Add the Github Access Token var resp = UrlFetchApp.fetch(url, {method: "patch", payload: JSON.stringify(payload)}); var bidItem = FormApp.getActiveForm().getItems()[0].setHelpText("Your bid may not exceed the lowest bid amount. Currently, the >>> lowest bid amount is $" + amt + "<<<.") } ``` -- What's going on here? -- Get the Form Responses ``` js var formResponses = FormApp.getActiveForm().getResponses(); var res = formResponses[formResponses.length-1].getItemResponses(); var amt = res[0].getResponse(); ``` -- Change title in GitHub ``` js var title = "Load Schedule 70 data into CALC. >>> Current bid: " + amt + " <<<" var payload = { "title": title } var url = "https://api.github.com/repos/18f/calc/issues/255?access_token=" + ACCESS_TOKEN // Add the Github Access Token var resp = UrlFetchApp.fetch(url, {method: "patch", payload: JSON.stringify(payload)}); ``` -- That's right, you can PATCH to GitHub from Google Apps -- -- And I can even change the current Form, because Google Apps! ``` js var bidItem = FormApp.getActiveForm().getItems()[0] .setHelpText("Your bid may not exceed the lowest bid amount. Currently, the >>> lowest bid amount is $" + amt + "<<<.") ``` -- But, the google script was a little buggy... -- So, I had to open the response sheet first. ¯\\\_(ツ)\_/¯ ``` js var ss = SpreadsheetApp.openById(ssID) // replace the ssID with the Spreadsheet ID var sheet = ss.getSheetByName("Form Responses 1"); ``` -- --- # What's next? -- So, here's the thing. -- Google Apps Script supports all of the Google Apps -- -- That means we could: * schedule meetings from slack! * auto-publish from docs to github! * automate onboarding! * automate mandatory training compliance! * and more! -- --- Anything that involves a REST API suddenly becomes interoperable with: * your word processor * your spreadsheet * your email * you calendar -- The future of business automation? Probably not. -- But I sure am glad that the public sharing was set to off. -- -- Thank you!